package websem.database;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Properties;
import java.util.Set;

import websem.handlers.DocumentHandler;
import websem.model.Paragraph;
import websem.model.Term;

/**
 * Database filler
 * @author Jissay
 *
 */
public class DatabaseFiller {

	private Connection connection;
	private DocumentHandler doc_handler;
	
	private static String PROPERTIES_FILE = "res/config.properties";
	
	/**
	 * Constructor
	 */
	public DatabaseFiller () {
		try {
			this.connection = null;
			Properties prop = new java.util.Properties();
			prop.load(new FileInputStream(PROPERTIES_FILE));
			
			// Connect to database
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			this.connection = DriverManager.getConnection(prop.getProperty("connectionUrl"), 
														  prop.getProperty("connectionUser"),
														  prop.getProperty("connectionPassword"));
			this.connection.setAutoCommit(false);
			
			// Test connection
			if (!this.connection.isClosed()) {
				System.out.println("--- Connection success.");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	/**
	 * Fill the database with local data from the documents in the specified PATH
	 */
	public void fillDatabase() {
		
		try {

			// Get document list
			this.doc_handler = new DocumentHandler();
			
			// Clear database table before re-inserting values
			this.clearDatabase();
			
			// Insert each document in database
			for (File doc : this.doc_handler.getDocuments_list()) {
				this.insertDocument(doc);
			}
			
			this.connection.commit();
			System.out.println("--- Documents inserted.");
			
			// Insert paragraphs into DB with document label
			for (Paragraph paragraph : this.doc_handler.getParagraphs_list()) {
				this.insertParagraph(paragraph);
			}
		
			this.connection.commit();
			System.out.println("--- Paragraphs insterted.");
			
			// Insert terms into DB with terms infos
			Set<String> keys = this.doc_handler.getTerms_list().keySet();
			for (String k : keys) {
				// Set id after insertion
				Term term = this.doc_handler.getTerms_list().get(k);
				term.setId(this.insertTerm(term));	
				
				// Insert infos about term
				Set<Paragraph> paragraphs_info = term.getInfos().keySet();
				for (Paragraph p : paragraphs_info) {
					// Insert contain
					long idContain = this.insertContain(term.getId(), p, term.getInfos().get(p).getWeight());
					
					// Insert position
					ArrayList<Integer> positions = term.getInfos().get(p).getPositions();
					for (Integer position : positions) {
						this.insertPosition(idContain, position);
					}
				}
			}
			
			this.connection.commit();
			System.out.println("--- Terms with informations insterted.");
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			
			// Close connection
			try {
				if(this.connection != null) { this.connection.close(); }
			} catch(SQLException e) {
				e.printStackTrace();
			}
	    }
	}

	/**
	 * Insert a term in database
	 * @param term
	 * @return
	 * @throws Exception
	 */
	public int insertTerm(Term term) throws Exception {
		// Prepare statements for inserting terms
		String insert_term_sql = "INSERT INTO Term (Label) values(?)";
		PreparedStatement insert_term_statement = this.connection.prepareStatement(insert_term_sql);
		insert_term_statement.setString(1, term.getLabel());			
		insert_term_statement.executeUpdate();
		
		ResultSet generatedKeys = insert_term_statement.getGeneratedKeys();
		generatedKeys.next();
		return generatedKeys.getInt(1);
	}
	
	/**
	 * Insert a document in database
	 * @param document
	 * @return
	 * @throws Exception
	 */
	public void insertDocument(File document) throws Exception {
		// Prepare statements for inserting documents
		String insert_doc_sql = "INSERT INTO Document (Label) VALUES(?)";
		PreparedStatement insert_doc_statement = this.connection.prepareStatement(insert_doc_sql);
		insert_doc_statement.setString(1, document.getName());			
		insert_doc_statement.executeUpdate();
	}
	
	/**
	 * Insert a paragraph in database
	 * @param paragraph
	 * @throws Exception
	 */
	public long insertParagraph(Paragraph paragraph) throws Exception {
		// Prepare statements for inserting paragraphs
		String insert_paragraph_sql = "INSERT INTO Paragraph (XPath, LabelDocument, Weight) values (?, ?, 0)";
		PreparedStatement insert_paragraph_statement = this.connection.prepareStatement(insert_paragraph_sql);

		// Parameter 1 is XPath, Parameter 2 is the document label
		insert_paragraph_statement.setString(1, paragraph.getxPath());
		insert_paragraph_statement.setString(2, paragraph.getDocumentName());
		insert_paragraph_statement.executeUpdate();
		
		ResultSet generatedKeys = insert_paragraph_statement.getGeneratedKeys();
		generatedKeys.next();
		return generatedKeys.getLong(1);
	}
	
	/**
	 * Insert a contain relation in database
	 * @return
	 * @throws Exception
	 */
	public long insertContain(long idTerm, Paragraph paragraph, double weight) throws Exception {
		// Prepare statements for inserting contains
		String insert_contain_sql = "INSERT INTO Contain (IDTerm, IDParagraph, Weight) values (?, (SELECT IDParagraph FROM Paragraph WHERE LabelDocument = ? and XPath = ?), ?)";
		PreparedStatement insert_contain_statement = this.connection.prepareStatement(insert_contain_sql);
		
		// Parameter 1 is IDTerm, Parameter 2 is Label Document, Parameter 3 is Xpath, Parameter 4 is isTitle
		insert_contain_statement.setLong(1, idTerm);
		insert_contain_statement.setString(2, paragraph.getDocumentName());
		insert_contain_statement.setString(3, paragraph.getxPath());
		insert_contain_statement.setDouble(4, weight);
		insert_contain_statement.executeUpdate();
		
		ResultSet generatedKeys = insert_contain_statement.getGeneratedKeys();
		generatedKeys.next();
		return generatedKeys.getLong(1);
	}
	
	/**
	 * Insert position into database
	 * @param idContain
	 * @param value
	 * @param word
	 * @return
	 * @throws Exception
	 */
	public long insertPosition(long idContain, int value) throws Exception {
		// Prepare statements for inserting contains
		String insert_position_sql = "INSERT INTO Position (IDContain, PositionValue) values (?, ?)";
		PreparedStatement insert_position_statement = this.connection.prepareStatement(insert_position_sql);
		
		// Parameter 1 is IDContain, Parameter 2 is PositionValue, Parameter 3 is Word
		insert_position_statement.setLong(1, idContain);
		insert_position_statement.setInt(2, value);
		insert_position_statement.executeUpdate();
		
		ResultSet generatedKeys = insert_position_statement.getGeneratedKeys();
		generatedKeys.next();
		return generatedKeys.getLong(1);
	}
	
	/**
	 * Clear the database
	 * @throws SQLException
	 */
	public void clearDatabase() throws SQLException {
		Statement clear_statement = this.connection.createStatement();
		clear_statement.executeUpdate("DELETE FROM Position");
		clear_statement.executeUpdate("DELETE FROM Contain");
		clear_statement.executeUpdate("DELETE FROM Paragraph");
		clear_statement.executeUpdate("DELETE FROM Document");
		clear_statement.executeUpdate("DELETE FROM Term");
		clear_statement.close();

		System.out.println("--- Database cleared.");
	}

	public DocumentHandler getDocumentHandler() {
		return this.doc_handler;
	}
}
